﻿using System;
using System.Collections.Generic;
using System.Text;

namespace DusonFramework.DataAccess.Paging
{
    /// <summary>
    /// Row_Number()
    /// </summary>
    public class Paging_RowNumber : PagingAbstract
    {
        //set nocount on;
        //with t_pager as (
        //   select *,rn = ROW_NUMBER() OVER (ORDER BY id desc) FROM test_indexorder
        // )
        //SELECT id,name,content,co1,co2,co3,co4,co5 from t_pager WHERE rn between 19007 and 19057;

        /// <summary>
        /// 使用 Row_Number()分页
        /// 适合于SQL 2005
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fields"></param>
        /// <param name="filter"></param>
        /// <param name="sortField"></param>
        /// <param name="sortType"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public override string GetPagingSQL(string table, string fields, string filter, string sortField, int sortType, int pageIndex, int pageSize)
        {
            int p1 = pageSize * (pageIndex - 1) + 1;
            int p2 = p1 + pageSize - 1;

            StringBuilder sql = new StringBuilder();
            string where = string.Empty;

            string sortTypeString = string.Empty;
            if (sortType == 1)
            {
                sortTypeString = "asc";
            }
            else
            {
                sortTypeString = "desc";
            }


            //查询条件
            if (!string.IsNullOrEmpty(filter))
            {
                where = " where " + filter;
            }

            //sql.Append("set nocount on; ");
            sql.Append(string.Format("with Temp as (select temp_Index = ROW_NUMBER() OVER (ORDER BY {0} {1}), * from {2} {3})", sortField, sortTypeString, table, where));
            sql.Append(string.Format(" select {0} from Temp where temp_Index between {1} and {2}", fields, p1, p2));
            //sql.Append(" set nocount off; ");

            return sql.ToString();
        }

    }
}
